<?php
/**
 * Created by PhpStorm.
 * User: Shen
 * Date: 2016/7/2
 * Time: 12:37
 */

try{
    $pdo = new PDO('mysql:host=localhost;dbname=test','root','123456');
    //exec():执行一条sql语句并返回其受影响的记录的条数,如果没有受影响的条数，返回0
    //exec对于select没有作用
    $sql = <<<EOF
        CREATE TABLE IF NOT EXISTS admin(
           id INT UNSIGNED AUTO_INCREMENT KEY,
           username VARCHAR(20) NOT NULL UNIQUE,
           password CHAR(32) NOT NULL,
           email VARCHAR(30) NOT NULL
        );
EOF;
    $res = $pdo->exec($sql);   //主要用于增删改

    if ($res === false){
        echo $pdo->errorCode();
        echo '<hr />';
        $errInfo = $pdo->errorInfo();
        var_dump($errInfo);
    }

    //query() 执行一条sql语句,返回PDOStatement对象

    $sql = 'select * from user';

    $stmt=$pdo->query($sql); //主要用于查询
    //var_dump($stmt);
    foreach ($stmt as $row){
        print_r($row);
    }


    echo '<hr />';


    //另一种常用查询，预处理语句
    //prepare() 准备要执行的sql，返回PDOStatement对象
    //execute() 执行一条预处理语句
    $stmt=$pdo->prepare($sql);

    var_dump($stmt);

    echo "<hr />";

    $res = $stmt->execute();

    var_dump($res);

    echo "<hr />";

    //得到结果集中的一条记录
    $row = $stmt->fetch();
    print_r($row);

    echo "<hr />";

    //可以设置模式
    // $stmt->setFetchMode(PDO::FETCH_ASSOC);
    $all = $stmt->fetchAll(PDO::FETCH_ASSOC);
    print_r($all);


    //得到数据库连接属性 setAttribute()
    //设置数据库连接属性 getAttribute()


    $username = 'admin';
    $password = '123456';

    //PDOStatement对象的使用
    //quote()防止sql注入，rowCount()查询出的条数
    // $pdo->quote($username);

    //预处理占位
    $sql = "SELECT * FROM admin WHERE username=? and password =?";
    $stmt = $pdo->prepare($sql);
    //可以这样
    $stmt ->execute(array($username,$password));
    echo $stmt->rowCount();

    echo "<hr />";

    $username = 'root';
    $password = '123456';
    //绑定参数
    $sql = "INSERT admin(username,password) VALUES (:username,:password)";
    $stmt = $pdo->prepare($sql);
    $stmt -> bindParam(":username",$username);
    $stmt -> bindParam("password",$password);
    $stmt -> execute();


    $username = 'rooter';
    $password = '123456';
    //绑定参数
    $sql = "INSERT admin(username,password) VALUES (?,?)";
    $stmt = $pdo->prepare($sql);
    $stmt -> bindParam(1,$username);
    $stmt -> bindParam(2,$password);
    $stmt -> execute();


    //将值绑定到参数
    $username = 'rooters';
    $password = '123456';
    //绑定参数
    $sql = "INSERT admin(username,password) VALUES (?,?)";
    $stmt = $pdo->prepare($sql);
    $stmt -> bindValue(1,$username);
    $stmt -> bindValue(2,$password);
    $stmt -> execute();

    //绑定多个值
    $sql = "SELECT username,password FROM admin";
    $stmt = $pdo->prepare($sql);
    $stmt->execute();

    echo '结果集中的列数' . $stmt->columnCount();

    echo '<hr />';
    //测试用的
    print_r($stmt->getColumnMeta(0));

    echo "<hr />";

    $stmt->bindColumn(1,$username);
    $stmt->bindColumn(2,$password);

    while ($stmt->fetch(PDO::FETCH_BOUND)) {
        echo "用户名：" . $username . "密码：" . $password . "<br />";
    }

    echo "<hr />";

    //fetchColumn() 从结果集中的下一行返回单独地一列


    //nextRowset() 取出下一个结果集



    

}catch(PDOException $e){
    echo $e->getMessage();
}






